

/*	Table 2 Panel F: TS average of spreads of high-minus-low beta port and characteristics port	*/

options threads cpucount=8 sortsize=32G bufno=8;
dm log 'clear';

%let mainfolder=D:\Dropbox\Characteristics Factors\RFS_Replication\;

libname mylib "&mainfolder.\Data_Cleaned";

proc datasets library=work nolist kill;
run;
quit;

%let enddate='31DEC2023'd;

data ret;
set mylib.crsp;
where year(date)>=1950;
if (dlstcd = 500 | dlstcd <= 584 and dlstcd >= 520) & hexcd = 3 & (dlret = .S | dlret = .T | dlret = .P) then Ddlret = -.55;
else if (dlstcd = 500 | dlstcd <= 584 and dlstcd >= 520) & (hexcd = 1 | hexcd =2 ) & (dlret = .S | dlret = .T | dlret = .P) then Ddlret = -.3;
else if dlret = .S | dlret = .T | dlret = .A | dlret = .P then Ddlret = .;
else DDLRET=DLRET;
if Dlstcd=. or dlstcd=100 THEN ret=rET; 
ELSE ret=ddlret;
date=intnx('month',date,0,'e');
format date yymmddn8.;
me=abs(prc)*shrout/1000;*ME in millions;
prc=abs(prc);
keep permno date me ret prc exchcd shrcd siccd;
Run;

proc sort data=ret nodupkey;
by _all_;
where not missing(me);
run;

proc sort data=ret nodupkey;
by permno date descending ret;
run;

proc sort data=ret nodupkey;
by permno date;
run;

data lagme(keep=permno date lagme lagprc lagexchcd lagshrcd lagsiccd);
set ret;
date=intnx('month',date,1,'e');
format date yymmddn8.;
rename me=lagme prc=lagprc exchcd=lagexchcd shrcd=lagshrcd siccd=lagsiccd;
run;

proc sort data=lagme nodupkey;
by permno date;
where not missing(permno) and not missing(date) and not missing(lagme);
run;

data all;
merge lagme(in=in1) ret(keep=permno date ret in=in2);
by permno date;
if in1;
run;
*	Read in 10-minus-1 characteristics portfolios spread;
proc sort data=mylib.allspread out=char;
by date;
run;

/*	Beta portfolios spread and its correlation with characteristics portfolio spreads	*/
%macro betaport(factor=);

dm 'odsresults; clear';

proc datasets library=work nolist;
save char all all1 table1 table2 beta;
run;

%if &factor=umd or &factor=perf %then %do;

proc sort data=all1(keep=date permno lagme beta1_&factor ret lagexchcd lagshrcd lagprc freq_&factor) 
out=port1(keep=date permno lagme beta1_&factor ret lagexchcd rename=(lagexchcd=exchcd)) nodupkey;
by date permno;
where not missing(beta1_&factor) and &startdate<=date<=&enddate and 1<=lagexchcd<=3 and 10<=lagshrcd<=11 and 
freq_&factor>=&minday;
run;

%end;

%else %if &factor=pead %then %do;

proc sort data=all1(keep=date permno lagme beta1_&factor ret lagexchcd lagshrcd lagprc freq_&factor) 
out=port1(keep=date permno lagme beta1_&factor ret lagexchcd rename=(lagexchcd=exchcd)) nodupkey;
by date permno;
where not missing(beta1_&factor) and '01JUL1975'd<=date<=&enddate and 1<=lagexchcd<=3 and 10<=lagshrcd<=11 and 
freq_&factor>=&minday;
run;

%end;

%else %do;

proc sort data=all1(keep=date permno lagme beta1_&factor ret lagexchcd lagshrcd lagprc freq_&factor) 
out=port1(keep=date permno lagme beta1_&factor ret lagexchcd rename=(lagexchcd=exchcd)) nodupkey;
by date permno;
where not missing(beta1_&factor) and &startdate<=date<=&enddate and 1<=lagexchcd<=3 and 10<=lagshrcd<=11 and 
freq_&factor>=&minmonth;
run;

%end;

proc univariate data=port1(where=(not missing(beta1_&factor) and 1<=exchcd<=3)) noprint;
by date;
var beta1_&factor;
output out=nyse pctlpre=P_ pctlpts= 10 to 90 by 10;
run;

proc sort data=nyse;
by date;
run;

data port1(drop=P_10 P_20 P_30 P_40 P_50 P_60 P_70 P_80 P_90);
merge port1 nyse;
by date;
if not(beta1_&factor=.) and beta1_&factor<P_10 then cgogroup=0;
if P_10<=beta1_&factor<P_20 then cgogroup=1;
if P_20<=beta1_&factor<P_30 then cgogroup=2;
if P_30<=beta1_&factor<P_40 then cgogroup=3;
if P_40<=beta1_&factor<P_50 then cgogroup=4;
if P_50<=beta1_&factor<P_60 then cgogroup=5;
if P_60<=beta1_&factor<P_70 then cgogroup=6;
if P_70<=beta1_&factor<P_80 then cgogroup=7;
if P_80<=beta1_&factor<P_90 then cgogroup=8;
if beta1_&factor>=P_90 then cgogroup=9;
run;

data port1;
set port1;
portfolio=cgogroup;
if portfolio=. or date=. then delete;
drop cgogroup;
run;

proc sort data=port1;
by date portfolio;
run;

proc means data=port1 noprint;
var ret;
by date portfolio;
weight lagme;
output out=group_ret(drop=_type_ _Freq_) mean=ret;
run;

proc sql;
create table group_ret
as select date,portfolio,ret
from group_ret 
group by date
having count(portfolio)=10
order by date,portfolio;
quit;

proc sql;
create table spread
as select distinct a.date,(a.ret-b.ret) as ret
from group_ret as a,group_ret as b
where a.date=b.date and a.portfolio=9 and b.portfolio=0;
quit;

data spread1;
merge spread(in=in1) char(keep=date &factor in=in2);
by date;
if in1 and in2;
run;

proc corr data=spread1 outp=f noprint;
var ret &factor;
run;

data f;
set f;
where _type_='CORR' and lower(_name_)="&factor";
proxy="&factor";
keep ret proxy;
run;

proc sort data=spread1 out=date(keep=date) nodupkey;
by date;
run;

data firstdate;
set date;
if _n_=1;
run;

proc sort data=date;
by descending date;
run;

data lastdate;
set date;
if _n_=1;
run;
*	add date	;
proc sql;
create table f
as select distinct a.*,b.date as firstdate format=yymmddn8.
from f as a,firstdate as b;
quit;

proc sql;
create table f
as select distinct a.*,b.date as lastdate format=yymmddn8.
from f as a,lastdate as b;
quit;

proc append data=f base=table1 force;
run;

%mend betaport;

%macro all(mbeta=,dbeta=);

proc datasets library=work nolist;
save char all;
run;

data mbeta;
set &mbeta;
date=intnx('month',date,1,'e');
format date yymmddn8.;
drop beta1_umd beta1_pead beta1_perf freq_umd freq_pead freq_perf;
run;

proc sort data=mbeta nodupkey;
by permno date;
run;

data dbeta;
set &dbeta;
date=intnx('month',date,1,'e');
format date yymmddn8.;
run;

proc sort data=dbeta nodupkey;
by permno date;
run;

data beta;
merge mbeta(in=in1) dbeta(in=in2);
by permno date;
run;

proc sort data=beta nodupkey;
by permno date;
run;

data all1;
merge all(in=in1) beta(in=in3);
by permno date;
if in1 and in3;
run;

data table1;
format proxy $24.;
format ret 12.4;
format firstdate yymmddn8.;
format lastdate yymmddn8.;
run;

%betaport(factor=smb_ff5);
%betaport(factor=hml);
%betaport(factor=rmw);
%betaport(factor=cma);
%betaport(factor=ia);
%betaport(factor=roe);
%betaport(factor=pmu);
%betaport(factor=qmj);
%betaport(factor=umd);
%betaport(factor=mgmt);
%betaport(factor=perf);
%betaport(factor=fin);
%betaport(factor=pead);

*	avg with smb;
proc means data=table1 noprint;
var ret;
output out=f1(drop=_freq_ _type_) mean=;
run;

data f1;
set f1;
proxy="avg";
run;

*	avg without smb;
proc means data=table1 noprint;
where not(proxy='smb_ff5');
var ret;
output out=f2(drop=_freq_ _type_) mean=;
run;

data f2;
set f2;
proxy="avg_nosmb";
run;

data table1;
set table1(where=(not missing(ret))) f1 f2;
run;
*	correlation between H-L beta and character spread;
proc export data=table1 outfile="&mainfolder.\Result\Table2_PanelF.csv" replace;run;

%mend all;

%let lags=5;
%let startdate='01JUL1966'd;

%let minmonth=36;
%let minday=45;
%all(mbeta=mylib.beta1_60,dbeta=mylib.beta2_3);
***************************************************************************************;

